﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;

using YouHP.IDAL;
using YouHP.Model;
using YouHP.Model.Group;
using YouHP.Utility;

namespace YouHP.SqlServerDAL
{
    public class GroupDAL : IGroup
    {
        #region basic operations
        // 添加小圈子
        public bool AddGroup(GroupInfo info)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.GroupSP_AddGroup,
                info.GroupName,
                info.GroupType,
                info.GroupTypeName,
                info.DiseaseId,
                info.TreatmentId,
                info.SymptomId,
                info.LabcheckId,
                info.Area,
                info.ProvinceId,
                info.CityId,
                info.ProvinceName,
                info.CityName,
                info.Keyword,
                info.Introduction,
                info.Logo,
                info.Moderators,
                info.AppUserId,
                info.AppUserName,
                info.UserNum,
                info.TopicNum,
                info.ReplyNum,
                info.PostNum,
                info.LimitUser,
                info.LastPostTime,
                info.IsGoodGroup,
                info.Hits,
                info.CreatTime,
                info.BrowserPermission,
                info.JoinPermission,
                info.IsValid,
                info.TopicSortString
                ) as IDataReader;
            if (reader.Read()) info.Id = (int)reader.GetDecimal(0);
            return true;
        }

        public bool UpdateGroup(GroupInfo info)
        {
            return SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.GroupSP_UpdateGroup,
                info.Id,
                info.GroupName,
                info.GroupType,
                info.GroupTypeName,
                info.DiseaseId,
                info.TreatmentId,
                info.SymptomId,
                info.LabcheckId,
                info.Area,
                info.Keyword,
                info.Introduction,
                info.Logo,
                info.Moderators,
                info.AppUserId,
                info.AppUserName,
                info.LimitUser,
                info.BrowserPermission,
                info.JoinPermission,
                info.TopicSortString
                ) > 0;
        }

        public bool DeleteGroup(int[] ids)
        {
            bool retVal = false;
            string tmpCond = "";
            int cnt = ids.Length;
            if (cnt <= 0)
                return false;

            StringBuilder sb = new StringBuilder();
            sb.Append(" in (");
            sb.Append(ids[0]);

            for (int i = 1; i < cnt; i++)
            {
                sb.Append(", ");
                sb.Append(ids[i]);
            }
            sb.Append(")");

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                //1.删除圈子对应的回复信息/
                tmpCond = " [Id] in (SELECT [Reply].[Id] FROM [GROUP], [REPLY] "
                    + " WHERE [REPLY].[GroupId] = [GROUP].[Id] AND [GROUP].[Id] "
                    + sb.ToString() + " ) ";
                SqlHelper.ExecuteNonQuery(ts, Common.ReplySP_DelReply, tmpCond);

                //2.删除圈子对应的主题信息/
                tmpCond = " [Id] in (SELECT [Topic].[Id] FROM [GROUP], [Topic] "
                    + " WHERE [Topic].[GroupId] = [GROUP].[Id] AND [GROUP].[Id] "
                    + sb.ToString() + " ) ";
                SqlHelper.ExecuteNonQuery(ts, Common.TopicSP_DelTopic, tmpCond);

                //3.删除圈子信息(说明:对于小圈子用户,不需要删除,当用户点击时,提示圈子已不存在!)
                SqlHelper.ExecuteNonQuery(ts, Common.GroupSP_DelGroup, " [Id] " + sb.ToString() );
                
                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        public bool InvalidateGroup(int groupid)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {

                SqlHelper.ExecuteNonQuery(ts, Common.GroupSP_InvalidateGroup,groupid);

                ts.Commit(); 
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        public GroupInfo GetGroupInfo(int id)
        {
            string condStr = string.Format(@" [Group].[Id] = {0} ", id);
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.GroupSP_GetGroup,
                condStr) as IDataReader;
            return CBO.FillObject<GroupInfo>(reader);
        }

        public GroupInfo GetGroupInfoByName(string groupName)
        {
            string condStr = string.Format(@" [Group].[GroupName] = '{0}' ", groupName);
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.GroupSP_GetGroup,
                condStr) as IDataReader;
            return CBO.FillObject<GroupInfo>(reader);
        }

        // 取得推荐的大圈子
        public GroupInfo GetRecommendedSystemGroup(int dszid)
        {
            string condStr = string.Format(@" [Group].[GroupType] <> 0 and [Group].[DiseaseId]={0} ", dszid);
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.GroupSP_GetGroup,
                condStr) as IDataReader;
            return CBO.FillObject<GroupInfo>(reader);
        }

        // 更新圈子的点击次数
        public bool IncreaseGroupHits(int groupId)
        {
            return SqlHelper.ExecuteNonQuery(Common.ConnStr,
                Common.GroupSP_IncreaseGroupHitNum,
                groupId) > 0;
        }
        #endregion

        #region list and search operations
        private List<GroupInfo> GetGroupList(int pageSize, int pageIndex, string condStr, string orderStr, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.GetFullName(Common.GroupSP_GetGroupList),
                pageSize, pageIndex, condStr, orderStr);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }   
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<GroupInfo>(dr);
        }

        private List<GroupInfo> GetTopGroups(string condStr, string orderStr, int n)
        {
            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.GroupSP_GetTopGroups,
                n, condStr, orderStr) as IDataReader;
            return CBO.FillCollection<GroupInfo>(reader);
        }

        private List<GroupInfo> GetGroupListByUserId(int pageSize, int pageIndex, string condStr, string orderStr, out int count)
        {
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.GetFullName(Common.GroupSP_GetGroupsByUserId),
                pageSize, pageIndex, condStr, orderStr);

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<GroupInfo>(dr);
        }

        public List<GroupInfo> GetGroupList(int pageSize, int pageIndex, out int count)
        {
            return GetGroupList(pageSize, pageIndex, " [GroupType] = 0 ", " [PostNum] desc ", out count);
        }

        // 取得最新创建的n个圈子（小圈子）
        public List<GroupInfo> GetGroupListLatest(int n)
        {
            string condStr = string.Format(@" [GroupType] = 0 ");
            return GetTopGroups(condStr, " [CreatTime] desc ", n);
        }

        // 取得最热的n个圈子 根据主题+帖子数（小圈子）
        public List<GroupInfo> GetGroupListHotest(int n)
        {
            string condStr = string.Format(@" [GroupType] = 0 ");
            return GetTopGroups(condStr, " [PostNum] desc ", n);
        }

        // 取得某个用户创建的圈子（小圈子）
        public List<GroupInfo> GetGroupListByCreator(int pageSize, int pageIndex, int userId, out int count)
        {
            string condStr = string.Format(@" [GroupType] = 0 and [AppUserId]={0} ", userId);
            return GetGroupList(pageSize, pageIndex, condStr, " [CreatTime] desc ", out count);
        }

        // 取得某个用户加入的圈子角色列表(根据用户在圈子里的角色排序)（小圈子）
        public List<GroupInfo> GetGroupListByUserId(int pageSize, int pageIndex, int userId, out int count)
        {
            string condStr = string.Format(@" [Group].[GroupType] = 0 and [GroupUser].[UserId]={0} AND [GroupUser].[Role] <> {1} AND [GroupUser].[Role] >= 0 ",
                userId, Constants.RoleCreator);
            return GetGroupListByUserId(pageSize, pageIndex, condStr, " [GroupUser].[Role] desc ", out count);
        }

        //取得某个用户所在的所有圈子(角色排序)
        public List<GroupInfo> GetAllGroupsByUserId(int pageSize, int pageIndex, int userId, out int count)
        {
            string condStr = string.Format(@"[Group].[GroupType] = 0 and [GroupUser].[UserId]={0} AND [GroupUser].[Role]>=0", userId);
            return GetGroupListByUserId(pageSize, pageIndex, condStr, "[GroupUser].[Role] desc,[Group].[CreatTime] desc",out count);
        }


        // 根据圈子类型取得圈子（大圈子）
        public List<GroupInfo> GetSystemGroupList(int pageSize, int pageIndex, int type, out int count)
        {
            string condStr = string.Format(@" [GroupType] = {0} ", type);
            return GetGroupList(pageSize, pageIndex, condStr, " [CreatTime] desc ", out count);
        }

        // 取得系统的大圈子列表（大圈子）
        public List<GroupInfo> GetSystemGroupList(int pageSize, int pageIndex, out int count)
        {
            return GetGroupList(pageSize, pageIndex, @" [GroupType] > 0 ", " [CreatTime] desc ", out count);
        }
        #endregion

        #region IGroup 成員

        //根据条件搜索圈子
        public List<GroupInfo> GetGroupListBySearch(int pageSize, int pageIndex, GroupSearchCondition cond, out int groupCount)
        {
            return GetGroupList(pageSize, pageIndex, cond.ToSqlConditonString(), cond.ToOrderByString(), out groupCount);
        }

        #endregion

        #region IGroup 成員

       //添加圈子邀请
        public bool AddGroupInvite(GroupInviteInfo info)
        {

            return SqlHelper.ExecuteNonQuery(Common.ConnStr,
                 Common.GroupSP_AddGroupInvite,
                 info.GroupId,
                 info.GroupName,
                 info.PostUserId,
                 info.PostUserName,
                 info.ReceiveUserId,
                 info.PostTime,
                 info.IsView
                 ) > 0;

        }

        #endregion

        #region IGroup 成員

        //取得某用户得到的最新圈子邀请
        public List<GroupInviteInfo> GetNewGroupInviteListByUserId(int pageSize, int pageIndex, int UserId, out int count)
        {
            string condStr = string.Format(@" [ReceiveUserId] = {0} and [IsView] = 0 ", UserId);

            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.GetFullName(Common.GroupSP_GetGroupInviteList),
                pageSize, pageIndex, condStr, "[PostTime] desc");

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<GroupInviteInfo>(dr);
        }

        #endregion

        #region IGroup 成員

        //删除圈子邀请
        public bool DelGroupInvite(int id)
        {
            return SqlHelper.ExecuteNonQuery(Common.ConnStr,
            Common.GroupSP_DelGroupInvite,
            id
            ) > 0;
        }

        #endregion
    }
}

